# predict_to_csv.py
import joblib, math
import pandas as pd
from pyhive import hive

# 加载模型
coef = joblib.load('lr_std.model')

# Hive 连接
conn = hive.connect(host='hadoop01', port=10000, database='gd_qg', auth='NONE')
cur = conn.cursor()

# 预测函数
def predict(row):
    z = coef[0] + sum(c * v for c, v in zip(coef[1:], row))
    return 1 if 1.0 / (1.0 + math.exp(-z)) >= 0.5 else 0

# 取 Hive 的特征表
sql = """
SELECT data_id, user_id, product_id,
       txt_len, title_len, cate1_id, cate2_id, cate3_id,
       user_day30_cnt, pos_rate, rating, dt
FROM gd_qg.tmp_comment_features
"""
cur.execute(sql)
rows = cur.fetchall()

# 生成预测结果
results = []
for r in rows:
    features = [float(x) if x is not None else 0.0 for x in r[3:10]]
    pred = predict(features)
    results.append((r[0], r[1], r[2], pred, r[10], r[11]))  # data_id, user_id, product_id, rating_pred, rating, dt

# 保存到 CSV
df = pd.DataFrame(results, columns=["data_id","user_id","product_id","rating_pred","rating","dt"])
df.to_csv("comment_pred.csv", index=False)
print(">>> 预测结果已写入 comment_pred.csv")
